package edu.usc.cmb.iregulome.client;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class TableSchemaDoc {

	/**
	 * @param args
	 */
	public static void main(String[] args) {
		Connection connection = null;
		try {
			// Load the JDBC driver
			String driverName = "org.gjt.mm.mysql.Driver"; // MySQL MM JDBC
			// driver
			Class.forName(driverName);

			// Create a connection to the database
			String serverName = "zhoudb.usc.edu";
			String mydatabase = "seq_db";
			String url = "jdbc:mysql://" + serverName + "/" + mydatabase; // a
			// JDBC
			// url
			String username = "siyuanh";
			String password = "11";
			connection = DriverManager.getConnection(url, username, password);
			ResultSet rs = connection.getMetaData().getTables(null, null, null,
					null);

			StringBuffer sb = new StringBuffer("<html><body>");
			Statement stat = connection.createStatement();
			while (rs.next()) {
				String tableName = rs.getString(3);
				sb
						.append("<table width=\"100%\"><tr bgcolor=\"#AFDBFF\"><td><font size='4'>Schema for: "
								+ tableName + "</td></tr><tr><td>");

				sb.append("<b>Database:</b> " + rs.getString(1)
						+ "&nbsp;&nbsp;&nbsp;&nbsp;<b>Table:" + "</b> "
						+ tableName
						+ "&nbsp;&nbsp;&nbsp;&nbsp;<b>Description:</b>"
						+ " <br>" + rs.getString("REMARKS"));
				sb
						.append("<table bgcolor=\"#fffee8\" border=\"1\" cellspacing=\"0\"><tbody><tr>"
								+ "<th>field</th><th>example</th><th>SQL type</th> <th>Default</th><th>info</th> <th>description</th> </tr>");
				ResultSet rsInner = connection.getMetaData().getColumns(null,
						null, tableName, null);
				ResultSet rsInner2 = stat.executeQuery("desc " + tableName);

				int a1 = rsInner.getMetaData().getColumnCount();
				for (int i = 1; i <= a1; i++) {
					System.out.println(rsInner.getMetaData().getColumnName(i));
				}
				while (rsInner.next() && rsInner2.next()) {
					sb.append("<tr>");
					sb.append("<td>" + rsInner.getString("COLUMN_NAME")
							+ "</td>");
					sb.append("<td>" + " " + "</td>");
					sb.append("<td>" + rsInner2.getString("Type") + "</td>");
					sb.append("<td>" + " " + "</td>");
					sb.append("<td>" + " " + "</td>");
					sb.append("<td>" + rsInner.getString("REMARKS") + "</td>");
					sb.append("</tr>");
				}
				sb
						.append("</tbody></table></td></tr><tr bgcolor=\"#AFDBFF\"><font size=\"4\"><td>Sample Row:</td></tr><tr><td>"
								+ "<table bgcolor=\"#fffee8\" border=\"1\" cellspacing=\"0\"><tbody><tr>");
				ResultSet rsInner3;
				if(tableName.endsWith("STAT")){
					rsInner3 = stat.executeQuery("select * from "
							+ tableName);
				}else{
					rsInner3 = stat.executeQuery("select * from "
							+ tableName + " limit 10");
				}
				
				int cc = rsInner3.getMetaData().getColumnCount();
				for (int i = 1; i <= cc; i++) {
					sb.append("<th>" + rsInner3.getMetaData().getColumnName(i)
							+ "</th>");
				}
				sb.append("</tr>");
				while (rsInner3.next()) {
					sb.append("<tr>");
					for (int i = 1; i <= cc; i++) {
						sb.append("<td>" + rsInner3.getObject(i) + "</td>");
					}
					sb.append("</tr>");
				}

				sb.append("</table></td></tr></table><br><br><br>");

			}
			sb.append("</body></html>");
			System.out.println(sb.toString());
		} catch (Exception e) {

		} finally {
			try {
				connection.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
	}
}
